package users;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Supplier;

public class Manager {
	private Connection manage;
	private int sid;
	private String upc;
	
	public Manager (Connection manage){
		this.manage = manage;
	}
	
	public ResultSet listShipments() throws SQLException{
		
		Statement stmt = manage.createStatement();
		String query = "SELECT * FROM Shipment";
		ResultSet rs = stmt.executeQuery(query);
		return rs;
	}

	public ResultSet listItems(int sid) throws SQLException{
		this.sid = sid;
		
		Statement stmt = manage.createStatement();
		String query = "SELECT s.upc, s.supPrice, s.quantity FROM ShipItem s WHERE " + sid + " = s.sid";
		ResultSet rs = stmt.executeQuery(query);
		return rs;
	}
	
	public void updatePrice(String[] a, int[] b) throws SQLException {
		
		Statement stmt1 = manage.createStatement();
		int i=0;
		int newPrice;
		String oldUPC;
		while(a[i] != null){

			oldUPC = a[i];
			newPrice = b[i];
			String update = "UPDATE Item I SET I.sellPrice = " + newPrice + " WHERE I.upc = '" + oldUPC + "'";
			stmt1.execute(update);
			i++;
		}
		stmt1.close();
	}
	
	public void addSupplier(Supplier S) throws SQLException{
		String address = S.getAdd();
		String name = S.getName();
		String city = S.getCity();
		String status = S.getStatus();
		
		Statement stmt4= manage.createStatement();
		String insert = "INSERT into Supplier(name, address, city, status) VALUES ('" + name + "', '" + address + "', '" + city + "', '" + status + "')";
		stmt4.executeUpdate(insert);
		stmt4.close();
	}
	
	public void removeSupplier(String name) throws SQLException{
		
		Statement stmt5 = manage.createStatement();
		String delete = "DELETE FROM Supplier WHERE name = '" + name + "'";
		stmt5.executeUpdate(delete);
		stmt5.close();
	}

	public void insertSinger(String upc, String name) throws SQLException{
		Statement stmt300 = manage.createStatement();
		String insert = "INSERT into LeadSinger(upc, name) VALUES ('" + upc + "', '" + name + "')";
		stmt300.executeUpdate(insert);
		stmt300.close();
	}
	
	public void deleteSinger(String upc, String name) throws SQLException{
		Statement stmt301 = manage.createStatement();
		String delete = "DELETE FROM LeadSinger L Where L.name = '" + name + "' AND L.upc = '" + upc + "'";
		stmt301.executeUpdate(delete);
		stmt301.close();
	}
	
	public void addItem(String upc, String title, String type, String category,
			String company, String year, String sellPrice) throws SQLException{

		Statement stmt1 = manage.createStatement();
		stmt1.executeUpdate("INSERT into Item " + 
				"(upc, title, type, category, company, year, sellPrice) VALUES " +
				"('" + upc + "', '" + title + "', '" + type + "', '" + category + "', '" + company + "', '" +
				year + "', '" + sellPrice + "')");
		
		stmt1.close();
	}

	public void removeItem(String upc) throws SQLException{
		
		Statement stmt11 = manage.createStatement();
		String query = "DELETE FROM Item I WHERE I.upc = '" + upc + "'";
		stmt11.executeUpdate(query);
		stmt11.close();
	}
	
	public void insertShipitem(int sid, String upc, int supPrice, int quantity) throws SQLException {
		Statement stmt15 = manage.createStatement();
		String insert = "INSERT into ShipItem(sid, upc, supPrice, quantity) VALUES (" + sid + ", '" + upc + "', " + supPrice + ", " + quantity + ")";
		stmt15.executeUpdate(insert);
		stmt15.close();
	}
	
	public void deleteShipitem(int sid) throws SQLException{
		Statement stmt151 = manage.createStatement();
		String delete = "DELETE FROM ShipItem S WHERE S.sid = " + sid + "";
		stmt151.executeUpdate(delete);
		stmt151.close();
	}
	public void addStored(String name, String upc, int stock) throws SQLException{
		Statement stmt13 = manage.createStatement();
		String query2 = "SELECT * FROM Stored S WHERE S.name = '" + name + "' AND S.upc = '" + upc + "'";
		ResultSet rs = stmt13.executeQuery(query2);
		if(rs.next()){
			int tempStock = rs.getInt(stock);
			tempStock += stock;
			String update = "UPDATE Stored SET stock = " + tempStock + " WHERE S.upc = '" + upc + "' AND S.name = '" + name + "'";
			stmt13.executeUpdate(update);
		}
		else{
		String query = "INSERT into Stored(name, upc, stock) VALUES ('" + name + "', '" + upc + "', '" + stock + "')";
		stmt13.executeUpdate(query);
		}
		stmt13.close();
	}
	public void removeStoredItem(String name, String upc) throws SQLException{
		Statement stmt12 = manage.createStatement();
		String query = "DELETE FROM Stored S WHERE S.name = '" + name + "' AND S.upc = '" + upc + "'";
		stmt12.executeUpdate(query);
		stmt12.close();
	}
	public void insertShipment(int sid, String supName, String storeName, String date) throws SQLException{
		Statement stmt100 = manage.createStatement();
		String query = "INSERT into Shipment(sid, supName, storeName, shipDate) VALUES (" + sid + ", '" + supName + "', '" + storeName + "', +TO_DATE('" + date + "', 'YYYY-MM-DD'))"; 
		stmt100.executeUpdate(query);
		stmt100.close();
	}
	
	public void deletePurchase(int sid) throws SQLException{
		Statement stmt102 = manage.createStatement();
		String delete = "DELETE FROM Purchase WHERE receiptID = " + sid + "";
		stmt102.executeUpdate(delete);
		stmt102.close();
	}
	
	public void deletePurchaseItem (int sid, String upc ) throws SQLException{
		Statement stmt102 = manage.createStatement();
		String delete = "DELETE FROM PurchaseItem WHERE receiptID = " + sid + "AND upc = '" + upc + "'";
		stmt102.executeUpdate(delete);
		stmt102.close();
		
	}
	public void deleteShipment(int sid) throws SQLException{
		Statement stmt101 = manage.createStatement();
		String delete = "DELETE FROM Shipment S WHERE S.sid = " + sid + "";
		stmt101.executeUpdate(delete);
		stmt101.close();
	}
	
	public void insertStore(String name, String address, String type) throws SQLException{
		Statement stmt200 = manage.createStatement();
		String query = "INSERT into Store(name, address, type) VALUES ('" + name + "', '" + address + "', '" + type + "')";
		stmt200.executeUpdate(query);
		stmt200.close();
	}
	
	public void insertHasSong(String upc, String title) throws SQLException {
		Statement stmt0 = manage.createStatement();
		String query = "INSERT into HasSong(upc, title) VALUES ('" + upc + "', '" + title +"')";
		stmt0.executeUpdate(query);
		stmt0.close();
	}
	
	public void deleteHasSong(String upc, String title) throws SQLException{
		Statement stmt201 = manage.createStatement();
		String query = "DELETE FROM HasSong S WHERE S.upc = '" + upc + "' AND S.title = '" + title + "'";
		stmt201.executeUpdate(query);
		stmt201.close();
	}
	
	public void deleteStore(String name) throws SQLException{
		Statement stmt201 = manage.createStatement();
		String query = "DELETE FROM Store S WHERE S.name = '" + name + "'";
		stmt201.executeUpdate(query);
		stmt201.close();
	}
	public ResultSet createReport(String storeName, String date) throws SQLException{

		Statement stmtq = manage.createStatement();
		String query = "SELECT N.upc, I.category, I.sellPrice AS UnitPrice, sum(N.quantity) AS UnitsSold, sum(I.sellPrice * N.quantity) AS TotalValue "+
		"FROM Item I, PurchaseItem N, Purchase P, Stored S WHERE I.upc=N.upc AND N.upc=I.upc AND S.name = '" + storeName + "' AND N.receiptID = P.receiptID AND P.purchaseDate = +TO_DATE('" + date + "', 'YYYY-MM-DD') GROUP BY N.upc, I.category, I.sellPrice"; 
		//"EXISTS (SELECT pi.upc FROM PurchaseItem pi, Purchase p, Stored s WHERE s.name = '" + storeName + "' AND p.purchaseDate = '" + date + "' AND pi.upc = s.upc AND i.upc = pi.upc)"; 
		//System.out.println(query);
		ResultSet report = stmtq.executeQuery(query);
		return report;
	}
	
	/*public void countTotal(ResultSet rs) throws SQLException{
		int[] unitP = new int[100];
		int[] unitQ = new int[100];
		int i = 0;
		int j = 0;
		
		Statement stmtt = manage.createStatement();
		
		while(rs.next()){
			unitP[i] = rs.getInt("sellPrice");
			unitQ[i] = rs.getInt("quantity");
			
			int totalRev=unitP[i]*unitQ[i];
		}
		

	}*/
	public ResultSet listOrders() throws SQLException{
		
		Statement stmt = manage.createStatement();
		ResultSet result = stmt.executeQuery("SELECT * FROM Purchase");
		
		return result;
	}
	
	public ResultSet getOrderDetail(int receiptID) throws SQLException{
			
		Statement stmt = manage.createStatement();
		ResultSet result = stmt.executeQuery("SELECT upc, quantity FROM PurchaseItem " +
				"WHERE receiptID = '"+receiptID+"'" ) ;
		
		return result;
	}
	
	// remove from PurchaseItem the rows with the receiptID
	public boolean deliverPurchaseItem(int receiptID) throws SQLException{
		
		boolean success = true;
		int quantity=0;
		int stock=0;
		List<String> upc = new ArrayList<String>();
		List<Integer> difference = new ArrayList<Integer>();
		
		
		
		Statement stmt = manage.createStatement();
		
		ResultSet result;
		result = stmt.executeQuery("SELECT deliveredDate FROM Purchase " +
				"WHERE receiptID = '"+receiptID+"'");
		
		if (result.next()){
			if(!(result.getObject("deliveredDate") == null)){
				return false;
			}
		}
		
		result = stmt.executeQuery("SELECT p.upc, p.quantity, s.stock FROM PurchaseItem p, " +
				"Stored s WHERE p.upc = s.upc AND s.name = 'Warehouse' AND receiptID = '"+receiptID+"'" );
		
		while (result.next()){
			// get the quantity ordered and the stock in stored.
			quantity = Integer.parseInt(result.getObject("quantity").toString());
			stock = Integer.parseInt(result.getObject("stock").toString());
			
			if (stock < quantity){
				success = false;
				return success;
			}
			
			// store the stock - quantity in difference array.
			upc.add(result.getObject("upc").toString());
			difference.add(stock-quantity);
		}

		Date todayDate = new Date(System.currentTimeMillis());
		
		for(int i=0; i<difference.size(); i++){
			stmt.executeUpdate("UPDATE Stored SET stock = "+difference.get(i)+" " +
					"WHERE upc = '"+upc.get(i)+"' AND name = 'Warehouse'");
			stmt.executeUpdate("UPDATE Purchase SET deliveredDate = TO_DATE('"+todayDate+"', 'YYYY-MM-DD')" +
					"WHERE receiptID = '"+receiptID+"'");
		}
		
		return success;
		
	}
	
	public void removeReturn(String retId) throws SQLException{
		Statement stmt = manage.createStatement();
		String query = "DELETE FROM Return R WHERE R.retid = '" + retId + "'";
		stmt.executeUpdate(query);
		stmt.close();
	}
	
	public void removeReturnItem(String retId, String upc) throws SQLException{
		Statement stmt = manage.createStatement();
		String query = "DELETE FROM ReturnItem R WHERE R.retid = '" + retId + "' AND R.upc = '"+upc+"'";
		stmt.executeUpdate(query);
		stmt.close();
	}
	
	
}